import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.poifs.crypt.Decryptor;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.poifs.crypt.dsig.*;



import java.io.File;
import java.io.IOException;
import java.util.Iterator;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * Created by rajeevkumarsingh on 18/12/17.
 */

public class ExcelReader {
    //public static final String SAMPLE_XLS_FILE_PATH = "./sample-xls-file.xls";
    public static final String SAMPLE_XLSX_FILE_PATH = "./sample-xlsx-file-with-key-with-cer.xlsx";

    public static void main(String[] args) throws IOException, InvalidFormatException {
        System.out.println("begin");
        // Creating a Workbook from an Excel file (.xls or .xlsx)
        //Workbook workbook = WorkbookFactory.create(new File(SAMPLE_XLSX_FILE_PATH));

        //org.apache.poi.hssf.record.crypto.Biff8EncryptionKey.setCurrentUserPassword(password);
        // Retrieving the number of sheets in the Workbook
        //System.out.println("Workbook has " + workbook.getNumberOfSheets() + " Sheets : ");

        String excelPath = SAMPLE_XLSX_FILE_PATH; //"Excel文件路徑";
        String password = "123456"; //"Excel文件密碼";
        String prefix = excelPath.substring(excelPath.lastIndexOf(".") + 1);
        System.out.println(prefix);

        //Workbook workbook;//xlsx

        InputStream inp = new FileInputStream(excelPath);

        if (prefix.toUpperCase().equals("XLS")) {//xls
            System.out.println("xls is not safe!" );
//            org.apache.poi.hssf.record.crypto.Biff8EncryptionKey
//                    .setCurrentUserPassword(password);
//            workbook = WorkbookFactory.create(inp);
            inp.close();
        } else {//xlsx
            //OPCPackage pkg = POIXMLDocument.openPackage(excelPath);
//            SignatureConfig sic = new SignatureConfig();
//            sic.setOpcPackage(pkg);
//            SignatureInfo si = new SignatureInfo();
//            si.setSignatureConfig(sic);
//            boolean isValid = si.verifySignature();
//            System.out.println(isValid);

            try{
                //workbook = WorkbookFactory.create(inp,password);
                //workbook = new XSSFWorkbook(inp);
           POIFSFileSystem pfs = new POIFSFileSystem(inp);
                inp.close();

            EncryptionInfo encInfo = new EncryptionInfo(pfs);
            Decryptor decryptor = Decryptor.getInstance(encInfo);

                decryptor.verifyPassword(password);

                XSSFWorkbook workbook = new XSSFWorkbook(decryptor.getDataStream(pfs));


                OPCPackage pkg = workbook.getPackage();
                SignatureConfig sic = new SignatureConfig();
                sic.setOpcPackage(pkg);
                SignatureInfo si = new SignatureInfo();
                si.setSignatureConfig(sic);
                boolean isValid = si.verifySignature();
                System.out.println(isValid);



                 /*
           =============================================================
           Iterating over all the sheets in the workbook (Multiple ways)
           =============================================================
        */

//        // 1. You can obtain a sheetIterator and iterate over it
//        Iterator<Sheet> sheetIterator = workbook.sheetIterator();
//        System.out.println("Retrieving Sheets using Iterator");
//        while (sheetIterator.hasNext()) {
//            Sheet sheet = sheetIterator.next();
//            System.out.println("=> " + sheet.getSheetName());
//        }
//
//        // 2. Or you can use a for-each loop
//        System.out.println("Retrieving Sheets using for-each loop");
//        for(Sheet sheet: workbook) {
//            System.out.println("=> " + sheet.getSheetName());
//        }

                // 3. Or you can use a Java 8 forEach wih lambda
                System.out.println("Retrieving Sheets using Java 8 forEach with lambda");
                workbook.forEach(sheet -> {
                    System.out.println("=> " + sheet.getSheetName());
                });

        /*
           ==================================================================
           Iterating over all the rows and columns in a Sheet (Multiple ways)
           ==================================================================
        */

                // Getting the Sheet at index zero
             //   Sheet sheet = workbook.getSheetAt(0);

//        // Create a DataFormatter to format and get each cell's value as String
//        DataFormatter dataFormatter = new DataFormatter();
//
//        // 1. You can obtain a rowIterator and columnIterator and iterate over them
//        System.out.println("\n\nIterating over Rows and Columns using Iterator\n");
//        Iterator<Row> rowIterator = sheet.rowIterator();
//        while (rowIterator.hasNext()) {
//            Row row = rowIterator.next();
//
//            // Now let's iterate over the columns of the current row
//            Iterator<Cell> cellIterator = row.cellIterator();
//
//            while (cellIterator.hasNext()) {
//                Cell cell = cellIterator.next();
//                String cellValue = dataFormatter.formatCellValue(cell);
//                System.out.print(cellValue + "\t");
//            }
//            System.out.println();
//        }
//
//        // 2. Or you can use a for-each loop to iterate over the rows and columns
//        System.out.println("\n\nIterating over Rows and Columns using for-each loop\n");
//        for (Row row: sheet) {
//            for(Cell cell: row) {
//                String cellValue = dataFormatter.formatCellValue(cell);
//                System.out.print(cellValue + "\t");
//            }
//            System.out.println();
//        }

                // 3. Or you can use Java 8 forEach loop with lambda
//                System.out.println("\n\nIterating over Rows and Columns using Java 8 forEach with lambda\n");
//                sheet.forEach(row -> {
//                    row.forEach(cell -> {
//                        printCellValue(cell);
//                    });
//                    System.out.println();
//                });

                // Closing the workbook
                workbook.close();


            }catch(Exception e){
                System.out.println("passwordWrong!");
                e.printStackTrace();

            }

        }




    }

//    private static void printCellValue(Cell cell) {
//        switch (cell.getCellTypeEnum()) {
//            case BOOLEAN:
//                System.out.print(cell.getBooleanCellValue());
//                break;
//            case STRING:
//                System.out.print(cell.getRichStringCellValue().getString());
//                break;
//            case NUMERIC:
//                if (DateUtil.isCellDateFormatted(cell)) {
//                    System.out.print(cell.getDateCellValue());
//                } else {
//                    System.out.print(cell.getNumericCellValue());
//                }
//                break;
//            case FORMULA:
//                System.out.print(cell.getCellFormula());
//                break;
//            case BLANK:
//                System.out.print("");
//                break;
//            default:
//                System.out.print("");
//        }
//
//        System.out.print("\t");
//    }
}
